package cn.roadtrip.mtravelbusinessmodule.bs.dispatch;

import cn.hutool.core.date.DateUnit;
import cn.roadtrip.mtravelbusinessmodule.sql.couple.dto.BsUserLayoutUserInforDTO;
import cn.roadtrip.mtravelbusinessmodule.sql.couple.traveldispatch.tables.records.BsTraveldispatchCarleaderdispatchrecordRecord;
import cn.roadtrip.mtravelbusinessmodule.sql.couple.traveldispatch.vpo.VPO_LeaderDispatchRecord;
import cn.roadtrip.mtravelbusinessmodule.sql.couple.travelgroup.dto.BsTravelGroupTourTravelLineFinalBindCarRecordDTO;
import cn.roadtrip.mtravelbusinessmodule.sql.couple.travelgroup.tables.records.BsTravelgroupMaintuortravellinerecordRecord;
import cn.roadtrip.mtravelbusinessmodule.sql.couple.travelgroup.tables.records.BsTravelgroupTourtravellinefinalbindcarrecordRecord;
import cn.roadtrip.utilmodule.util.DataBean;
import cn.roadtrip.utilmodule.util.enumpck.*;
import com.github.yitter.idgen.YitIdHelper;
import com.google.gson.Gson;
import lombok.*;
import lombok.extern.slf4j.Slf4j;
import org.jooq.DSLContext;
import org.jooq.Table;
import org.jooq.impl.DSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.HashMap;

import static cn.roadtrip.mtravelbusinessmodule.sql.couple.Tables.BS_USERLAYOUT_USERINFOR;
import static cn.roadtrip.mtravelbusinessmodule.sql.couple.traveldispatch.tables.BsTraveldispatchCarleaderdispatchrecord.BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD;
import static cn.roadtrip.mtravelbusinessmodule.sql.couple.travelgroup.Tables.BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD;
import static cn.roadtrip.mtravelbusinessmodule.sql.couple.travelgroup.Tables.BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD;

/**
 * 行程调度
 */
@Component
@Slf4j
public class TravelDispatchImpl {

    @Autowired
    private DSLContext dslContext;

    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @EqualsAndHashCode
    public static class Model_queryFreeTravelLeaderList {
        private String travelGroupId;//行程记录ID
        private Integer cpage;
        private Integer pagesize;
    }

    /**
     * 查询空闲的领队列表
     *
     * @return
     */
    public DataBean queryFreeTravelLeaderList(
            Model_queryFreeTravelLeaderList model
    ) {
        BsTravelgroupMaintuortravellinerecordRecord maintuortravellinerecordRecord = dslContext.selectFrom(BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD)
                .where(
                        BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.ID.eq(model.getTravelGroupId())
                ).fetchOne();
        if (maintuortravellinerecordRecord == null) {
            return DataBean.err("not-exist", "行程不存在");
        }
        long startDate = maintuortravellinerecordRecord.getStartdate();
        long endDate = (maintuortravellinerecordRecord.getTraveldays() - 1) * DateUnit.DAY.getMillis() + startDate;
        int count = dslContext.resultQuery("\n" +
                        "                                SELECT\n" +
                        "                ifnull(count(BS_UserLayout_UserInfor.id),0) as n\n" +
                        "                FROM\n" +
                        "                BS_UserLayout_UserInfor,\n" +
                        "                BS_UserLayout_UserBindRole\n" +
                        "                WHERE\n" +
                        "                BS_UserLayout_UserBindRole.trash =FALSE\n" +
                        "                and BS_UserLayout_UserBindRole.stats = {0}\n" +
                        "                and BS_UserLayout_UserBindRole.roleType = {1}\n" +
                        "                and BS_UserLayout_UserInfor.stats = {2}\n" +
                        "                and BS_UserLayout_UserInfor.id = BS_UserLayout_UserBindRole.userId\n" +
                        "                and BS_UserLayout_UserInfor.id not in (\n" +
                        "\n" +
                        "                SELECT DISTINCT\n" +
                        "                \tbcr.bindLeaderId\n" +
                        "                FROM\n" +
                        "                \t(\n" +
                        "                \tSELECT\n" +
                        "                \t\tt3.gid,\n" +
                        "                \tIF\n" +
                        "                \t\t(\n" +
                        "                \t\t\t( t3.matchLength - t3.len )= 0,\n" +
                        "                \t\tIF\n" +
                        "                \t\t\t( t3.startDate = t3.endDate,-1, 0 ),\n" +
                        "                \t\t\t( t3.matchLength - t3.len )\n" +
                        "                \t\t) AS v\n" +
                        "                \tFROM\n" +
                        "                \t\t(\n" +
                        "                \t\tSELECT\n" +
                        "                \t\t\tt2.gid,\n" +
                        "                \t\t\tt2.startDate,\n" +
                        "                \t\t\tt2.endDate,\n" +
                        "                \t\t\t( t2.max - t2.min ) AS matchLength,\n" +
                        "                \t\t\tt2.min,\n" +
                        "                \t\t\tt2.max,\n" +
                        "                \t\t\tt2.len\n" +
                        "                \t\tFROM\n" +
                        "                \t\t\t(\n" +
                        "                \t\t\tSELECT\n" +
                        "                \t\t\t\tt1.gid,\n" +
                        "                \t\t\t\tt1.startDate,\n" +
                        "                \t\t\t\tt1.endDate,\n" +
                        "                \t\t\tIF\n" +
                        "                \t\t\t\t( t1.startDate < {4}, t1.startDate, {5} ) AS min,\n" +
                        "                \t\t\tIF\n" +
                        "                \t\t\t\t( t1.endDate > {6}, t1.endDate, {7} ) AS max,\n" +
                        "                \t\t\t\t( t1.endDate - t1.startDate + {8}-{9} ) AS len\n" +
                        "                \t\t\tFROM\n" +
                        "                \t\t\t\t(\n" +
                        "                \t\t\t\tSELECT\n" +
                        "                \t\t\t\t\tr1.id AS gid,\n" +
                        "                \t\t\t\t\tr1.startDate,\n" +
                        "                \t\t\t\t\tr1.startDate +(\n" +
                        "                \t\t\t\t\t\tr1.travelDays - 1\n" +
                        "                \t\t\t\t\t)* 86400000 AS endDate\n" +
                        "                \t\t\t\tFROM\n" +
                        "                \t\t\t\t\tBS_TravelGroup_MainTuorTravelLineRecord AS r1\n" +
                        "                \t\t\t\tWHERE\n" +
                        "                \t\t\t\t\tr1.stats IN ( {10}, {11},{12}, {13} )\n" +
                        "                \t\t\t\t) AS t1\n" +
                        "                \t\t\t) AS t2 \n" +
                        "                \t\t) AS t3 \n" +
                        "                \t) AS t4,\n" +
                        "                \t( SELECT BS_TravelGroup_TourTravelLineFinalBindCarRecord.travelGroupId, BS_TravelGroup_TourTravelLineFinalBindCarRecord.bindLeaderId FROM BS_TravelGroup_TourTravelLineFinalBindCarRecord WHERE BS_TravelGroup_TourTravelLineFinalBindCarRecord.trash = FALSE ) AS bcr\n" +
                        "                WHERE\n" +
                        "                \tbcr.travelGroupId = t4.gid\\s\n" +
                        "                \tAND t4.v <0\n" +
                        "                )\n",
                DSL.val(Enum_UserBindRoleStats.Active.getCode()),
                DSL.val(Enum_UserRoleType.Leader.getCode()),
                DSL.val(Enum_UserInforStats.Open.getCode()),
                DSL.val(startDate),
                DSL.val(startDate),
                DSL.val(endDate),
                DSL.val(endDate),
                DSL.val(endDate),
                DSL.val(startDate),
                DSL.val(Enum_MainTourTravelLineStats.Wait.getCode()),
                DSL.val(Enum_MainTourTravelLineStats.ApplyOk.getCode()),
                DSL.val(Enum_MainTourTravelLineStats.Freze.getCode()),
                DSL.val(Enum_MainTourTravelLineStats.WaitOwner.getCode())
        ).fetchOne().into(Integer.class);
        if (count == 0)
            return DataBean.sucEmpty();

        //时间段内，没有被安排的领队
        java.util.List<String> leaderIds = dslContext.resultQuery("\n" +
                        "                                SELECT\n" +
                        "                BS_UserLayout_UserInfor.id\n" +
                        "                FROM\n" +
                        "                BS_UserLayout_UserInfor,\n" +
                        "                BS_UserLayout_UserBindRole\n" +
                        "                WHERE\n" +
                        "                BS_UserLayout_UserBindRole.trash =FALSE\n" +
                        "                and BS_UserLayout_UserBindRole.stats = {0}\n" +
                        "                and BS_UserLayout_UserBindRole.roleType = {1}\n" +
                        "                and BS_UserLayout_UserInfor.stats = {2}\n" +
                        "                and BS_UserLayout_UserInfor.id = BS_UserLayout_UserBindRole.userId\n" +
                        "                and BS_UserLayout_UserInfor.id not in (\n" +
                        "\n" +
                        "                SELECT DISTINCT\n" +
                        "                \tbcr.bindLeaderId\n" +
                        "                FROM\n" +
                        "                \t(\n" +
                        "                \tSELECT\n" +
                        "                \t\tt3.gid,\n" +
                        "                \tIF\n" +
                        "                \t\t(\n" +
                        "                \t\t\t( t3.matchLength - t3.len )= 0,\n" +
                        "                \t\tIF\n" +
                        "                \t\t\t( t3.startDate = t3.endDate,-1, 0 ),\n" +
                        "                \t\t\t( t3.matchLength - t3.len )\n" +
                        "                \t\t) AS v\n" +
                        "                \tFROM\n" +
                        "                \t\t(\n" +
                        "                \t\tSELECT\n" +
                        "                \t\t\tt2.gid,\n" +
                        "                \t\t\tt2.startDate,\n" +
                        "                \t\t\tt2.endDate,\n" +
                        "                \t\t\t( t2.max - t2.min ) AS matchLength,\n" +
                        "                \t\t\tt2.min,\n" +
                        "                \t\t\tt2.max,\n" +
                        "                \t\t\tt2.len\n" +
                        "                \t\tFROM\n" +
                        "                \t\t\t(\n" +
                        "                \t\t\tSELECT\n" +
                        "                \t\t\t\tt1.gid,\n" +
                        "                \t\t\t\tt1.startDate,\n" +
                        "                \t\t\t\tt1.endDate,\n" +
                        "                \t\t\tIF\n" +
                        "                \t\t\t\t( t1.startDate < {4}, t1.startDate, {5} ) AS min,\n" +
                        "                \t\t\tIF\n" +
                        "                \t\t\t\t( t1.endDate > {6}, t1.endDate, {7} ) AS max,\n" +
                        "                \t\t\t\t( t1.endDate - t1.startDate + {8}-{9} ) AS len\n" +
                        "                \t\t\tFROM\n" +
                        "                \t\t\t\t(\n" +
                        "                \t\t\t\tSELECT\n" +
                        "                \t\t\t\t\tr1.id AS gid,\n" +
                        "                \t\t\t\t\tr1.startDate,\n" +
                        "                \t\t\t\t\tr1.startDate +(\n" +
                        "                \t\t\t\t\t\tr1.travelDays - 1\n" +
                        "                \t\t\t\t\t)* 86400000 AS endDate\n" +
                        "                \t\t\t\tFROM\n" +
                        "                \t\t\t\t\tBS_TravelGroup_MainTuorTravelLineRecord AS r1\n" +
                        "                \t\t\t\tWHERE\n" +
                        "                \t\t\t\t\tr1.stats IN ( {10}, {11},{12}, {13} )\n" +
                        "                \t\t\t\t) AS t1\n" +
                        "                \t\t\t) AS t2 \n" +
                        "                \t\t) AS t3 \n" +
                        "                \t) AS t4,\n" +
                        "                \t( SELECT BS_TravelGroup_TourTravelLineFinalBindCarRecord.travelGroupId, BS_TravelGroup_TourTravelLineFinalBindCarRecord.bindLeaderId FROM BS_TravelGroup_TourTravelLineFinalBindCarRecord WHERE BS_TravelGroup_TourTravelLineFinalBindCarRecord.trash = FALSE ) AS bcr\n" +
                        "                WHERE\n" +
                        "                \tbcr.travelGroupId = t4.gid\\s\n" +
                        "                \tAND t4.v <0\n" +
                        "                )\n" +
                        "order by BS_UserLayout_UserInfor.id desc limit {14},{15}",
                DSL.val(Enum_UserBindRoleStats.Active.getCode()),
                DSL.val(Enum_UserRoleType.Leader.getCode()),
                DSL.val(Enum_UserInforStats.Open.getCode()),
                DSL.val(startDate),
                DSL.val(startDate),
                DSL.val(endDate),
                DSL.val(endDate),
                DSL.val(endDate),
                DSL.val(startDate),
                DSL.val(Enum_MainTourTravelLineStats.Wait.getCode()),
                DSL.val(Enum_MainTourTravelLineStats.ApplyOk.getCode()),
                DSL.val(Enum_MainTourTravelLineStats.Freze.getCode()),
                DSL.val(Enum_MainTourTravelLineStats.WaitOwner.getCode()),
                DSL.val(model.getCpage() * model.getPagesize()),
                DSL.val(model.getPagesize())
        ).fetch().into(String.class);

        java.util.List<BsUserLayoutUserInforDTO> userInfors = dslContext.selectFrom(
                BS_USERLAYOUT_USERINFOR
        ).where(
                BS_USERLAYOUT_USERINFOR.ID.in(
                        leaderIds
                )
        ).fetch().into(BsUserLayoutUserInforDTO.class);

        return DataBean.suc(userInfors, count, "suc", "操作成功");
    }


    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @EqualsAndHashCode
    public static class Model_dispatchLeader2TravelGroup {
        private String travelGroupId;//行程记录ID
        private String leaderId;//领队ID
    }

    //分配领队
    public DataBean dispatchLeader2TravelGroup(Model_dispatchLeader2TravelGroup model) {

        try {
            return dslContext.transactionResult(ctx -> {
                //00 查询出已有的绑定
                java.util.List<BsTravelGroupTourTravelLineFinalBindCarRecordDTO> bindrecordList = null;
                try {
                    bindrecordList = dslContext.selectFrom(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD)
                            .where(
                                    BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.TRASH.eq(false)
                                            .and(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.TRAVELGROUPID.eq(
                                                            model.getTravelGroupId()
                                                    )
                                            )
                            ).fetch().into(BsTravelGroupTourTravelLineFinalBindCarRecordDTO.class);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                //如果存在记录，则挑选出待调度还没有分配领队得记录，并更新记录
                if (bindrecordList != null) {
                    BsTravelGroupTourTravelLineFinalBindCarRecordDTO obj_record = null;
                    for (BsTravelGroupTourTravelLineFinalBindCarRecordDTO r : bindrecordList) {
                        if (r.getLeaderStats().equals(Enum_LeaderBindFinalCarStats.WaitDispatch.getCode())) {
                            obj_record = r;
                            break;
                        }
                    }
                    if (obj_record == null)
                        bindrecordList = null;
                    else {
                        //更新已有得记录
                        int count = dslContext.update(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD)
                                .set(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.BINDLEADERID, model.getLeaderId())
                                .set(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.LEADERSTATS, Enum_LeaderBindFinalCarStats.WaitApply.getCode())
                                .where(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.ID.eq(obj_record.getId()))
                                .execute();
                        if (count > 0) {
                            return DataBean.suc(true, 1, "suc", "操作成功");
                        } else {
                            return DataBean.err("update-fail", "更新失败");
                        }
                    }
                }
                //如果不存在记录，则直接新增
                if (bindrecordList == null) {
                    String rid = "" + YitIdHelper.nextId();
                    //新增车辆绑定
                    int count = dslContext.insertInto(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD)
                            .set(new BsTravelgroupTourtravellinefinalbindcarrecordRecord(
                                    rid,
                                    false,
                                    Enum_LeaderBindFinalCarStats.WaitApply.getCode(),
                                    Enum_CarBindFinalCarRecord.WaitDispatch.getCode(),
                                    model.getTravelGroupId(),
                                    "0",
                                    model.getLeaderId(),
                                    System.currentTimeMillis(),
                                    "{}",
                                    0,
                                    ""
                            ))
                            .execute();
                    if (count <= 0) {
                        throw new RuntimeException("最终绑定记录插入失败");
                    }
                    //新增领队绑定
                    String lid = "" + YitIdHelper.nextId();
                    count = dslContext.insertInto(BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD)
                            .set(
                                    new BsTraveldispatchCarleaderdispatchrecordRecord(
                                            lid,
                                            false,
                                            Enum_LeaderDispatchStats.Wait.getCode(),
                                            rid,
                                            model.getLeaderId(),
                                            System.currentTimeMillis(),
                                            "{}"
                                    )
                            ).execute();

                    if (count <= 0) {
                        throw new RuntimeException("领队分配记录失败");
                    }
                }
                dslContext.commit();
                return DataBean.suc(true, 1, "suc", "操作成功");
            });
        } catch (Exception e) {
            e.printStackTrace();
            return DataBean.err("trans-err", e.getMessage());
        }
    }


    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @EqualsAndHashCode
    public static class Model_doLeaderDispatchResult {
        private String leaderDispatchId;//领队分配记录ID
        private String finalBindCarRecordId;//最终绑定车辆记录ID
        private Enum_LeaderDispatchStats leaderDispatchStats;//领队分配状态
        private java.util.Map<String, Object> extInfor;//其他信息
    }

    /**
     * 领队处理是否带队
     *
     * @param model
     * @return
     */
    public DataBean doLeaderDispatchResult(Model_doLeaderDispatchResult model) {
        try {
            return dslContext.transactionResult(ctx -> {
                //更新车辆绑定记录以及领队分配记录
                int count = dslContext.update(BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD)
                        .set(
                                BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.LEADERSTATS,
                                model.getLeaderDispatchStats().getCode().equals(Enum_LeaderDispatchStats.Cancel.getCode()) ? Enum_LeaderBindFinalCarStats.WaitDispatch.getCode() : Enum_LeaderBindFinalCarStats.DispatchFinish.getCode()
                        ).where(
                                BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.TRASH.eq(false)
                                        .and(
                                                BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.ID.eq(model.getFinalBindCarRecordId())
                                        )
                        )
                        .execute();
                if (count <= 0)
                    throw new RuntimeException("更新车辆绑定记录失败");
                Gson gson = new Gson();
                model.setExtInfor(model.getExtInfor() == null ? new HashMap<>() : model.getExtInfor());
                count = dslContext.update(BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD)
                        .set(
                                BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.STATS, model.getLeaderDispatchStats().getCode()
                        ).set(BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.EXTINFOR, gson.toJson(model.getExtInfor()))
                        .where(
                                BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.TRASH.eq(false)
                                        .and(
                                                BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.ID.eq(model.getLeaderDispatchId())
                                        )
                        ).execute();
                if (count <= 0)
                    throw new RuntimeException("更新领队分配记录失败");
                dslContext.commit();
                return DataBean.suc(true, 1, "suc", "操作成功");
            });
        } catch (Exception e) {
            e.printStackTrace();
            return DataBean.err("trans-err", e.getMessage());
        }
    }

    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @EqualsAndHashCode
    public static class Model_queryLeaderTravelDispatchList {
        private String leaderId;//可选
        private Enum_LeaderDispatchStats leaderDispatchStats;//领队分配状态  可选
        private Integer pagesize;
        private Integer cpage;
    }

    /**
     * 查询领队分配记录
     *
     * @param model
     * @return
     */
    public DataBean queryLeaderTravelDispatchList(Model_queryLeaderTravelDispatchList model) {
        try {
            // 最终绑定车辆记录
            Table<?> t1 = dslContext.select(
                            BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.TRAVELGROUPID.as("travelGroupId"),
                            BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.LEADERSTATS.as("leaderStats"),
                            BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.CREATETIME.as("createTime"),
                            BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.ID.as("id"),
                            BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.BINDSTARTPOINT.as("bindStartPoint")
                    ).from(
                            " BS_TravelGroup_TourTravelLineFinalBindCarRecord FORCE index(index04) "
                    ).where(
                            BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.TRASH.eq(false)
                    ).and(model.getLeaderId() == null ? DSL.noCondition() : BS_TRAVELGROUP_TOURTRAVELLINEFINALBINDCARRECORD.BINDLEADERID.eq(model.getLeaderId()))
                    .asTable("t1");

            //团记录
            Table<?> t2 = dslContext.select(
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.ID.as("travelGroupId"),
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.STARTDATE.as("startDate"),
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.TRAVELTITLE.as("travelTitle"),
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.TYPE.as("type"),
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.DISPATCHSTATS.as("dispatchStats")
            ).from(
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD
            ).where(
                    BS_TRAVELGROUP_MAINTUORTRAVELLINERECORD.DISPATCHSTATS.in(
                            Enum_MainTourTravelLineStats.WaitOwner.getCode(),
                            Enum_MainTourTravelLineStats.Wait.getCode(),
                            Enum_MainTourTravelLineStats.ApplyOk.getCode(),
                            Enum_MainTourTravelLineStats.TravelOver.getCode(),
                            Enum_MainTourTravelLineStats.Start.getCode()
                    )
            ).asTable("t2");
            //车辆领队绑定记录
            Table<?> t3 = dslContext.select(
                            BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.ID.as("id"),
                            BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.FINALBINDCARRECORDID.as("finalBindCarRecordId")
                    ).from(
                            BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD
                    ).where(
                            BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.TRASH.eq(false)

                    ).and(
                            model.getLeaderId() == null ? DSL.noCondition() : BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.LEADERID.eq(model.getLeaderId())
                    )
                    .and(
                            model.getLeaderDispatchStats() == null ? DSL.noCondition() : BS_TRAVELDISPATCH_CARLEADERDISPATCHRECORD.STATS.eq(model.getLeaderDispatchStats().getCode())
                    ).asTable("t3");

            log.info("----- 统计完成 ------");
            /**
             * 统计条数
             */
            int count = dslContext.selectCount().from(
                    t1, t2, t3
            ).where(
                    t2.field("travelGroupId", String.class).eq(t1.field("travelGroupId", String.class))

            ).and(
                    t3.field("finalBindCarRecordId", String.class).eq(t1.field("id", String.class))
            ).fetchOne().value1();
            if (count == 0) {
                return DataBean.sucEmpty();
            }
            java.util.List<VPO_LeaderDispatchRecord> list = dslContext.select(
                            t1.field("leaderStats"),
                            t1.field("travelGroupId"),
                            t2.field("startDate"),
                            t2.field("travelTitle"),
                            t2.field("type"),
                            t2.field("dispatchStats"),
                            t3.field("leaderDispatchId"),
                            t1.field("finalBindCarRecordId"),
                            t1.field("bindStartPoint")
                    ).from(
                            t1, t2, t3
                    ).where(
                            t2.field("travelGroupId", String.class).eq(t1.field("travelGroupId", String.class))
                                    .and(
                                            t3.field("finalBindCarRecordId", String.class).eq(t1.field("id", String.class))
                                    )
                    ).orderBy(
                            t2.field("travelGroupId", String.class).desc()
                            , t1.field("createTime", String.class).desc()
                    )
                    .limit(model.getCpage() * model.getPagesize(), model.getPagesize())
                    .fetch().into(VPO_LeaderDispatchRecord.class);

            return DataBean.suc(list, count, "suc", "操作成功");
        } catch (Exception e) {
            e.printStackTrace();
            return DataBean.err("trans-err", e.getMessage());
        }

    }
}
